Set WshShell = WScript.CreateObject("WScript.Shell")
For i = 1 to 3

    Select Case i
        Case "1"
            scriptType = "Animals"
        Case "2"
            scriptType = "Humans"
        Case "3"
            scriptType = "COI"
    End Select
    scriptDirectory = WshShell.CurrentDirectory + "\" + scriptType
    xmlFile = scriptDirectory + "\Scripts.xml"

    Set oXMLDoc = CreateObject("Msxml2.DOMDocument.4.0")

    oXMLDoc.load( xmlFile )
    Set oNodes = oXMLDoc.selectNodes( "/Scripts/Script" )

    Set oFs = CreateObject("Scripting.FileSystemObject")
    Set oOutFile = oFs.OpenTextFile( scriptType + "Scripts.sql", 2, true )

    oOutFile.WriteLine "DECLARE @ScriptId int, @QVId int"
    For x = 0 to oNodes.Length - 1
        Set oNode = oNodes(x)
        Set oInFile = oFs.OpenTextFile( scriptDirectory + "\" + oNode.SelectSingleNode( "Filename" ).Text, 1, false )
        script = Replace( oInFile.ReadAll, "'", "''" )

        scriptTypeCode = oNode.SelectSingleNode( "Type" ).Text

        oOutFile.WriteBlankLines 1
        oOutFile.WriteLine "SELECT @QVId = qv.Id"
        oOutFile.WriteLine "    FROM questionnaireversion qv, questionnaire q"
        oOutFile.WriteLine "    WHERE qv.questionnaireid = q.id"
        oOutFile.WriteLine "    AND q.category = '" + oNode.SelectSingleNode( "QuestionnaireCategory" ).Text + "'"
        oOutFile.WriteLine "    AND q.name = '" + oNode.SelectSingleNode( "Name" ).Text + "'"
        oOutFile.WriteLine "    AND qv.active = 1"
        oOutFile.WriteLine "IF EXISTS (SELECT null FROM script s, script_questionnaireversion_map sqm "
        oOutFile.WriteLine "           WHERE s.Id = sqm.ScriptId AND sqm.questionnaireversionid = @QVId "
        oOutFile.WriteLine "              AND s.Name='" + scriptTypeCode + "' AND s.Type='" + scriptTypeCode + "')"
        oOutFile.WriteLine "    BEGIN"
        oOutFile.WriteLine "        UPDATE Script Set ScriptText="
        oOutFile.WriteLine "'" + script + "' "
        oOutfile.WriteLine "        WHERE Id = (Select s.Id FROM script s, script_questionnaireversion_map sqm "
        oOutFile.WriteLine "                                WHERE s.Id = sqm.ScriptId AND sqm.questionnaireversionid = @QVId "
        oOutFile.WriteLine "                                   AND s.Name='" + scriptTypeCode + "' AND s.Type='" + scriptTypeCode + "')"
        oOutFile.WriteLine "    END"
        oOutFile.WriteLine "ELSE"
        oOutFile.WriteLine "    BEGIN"
        oOutFile.WriteLine "        INSERT INTO script (name, type, scripttext) VALUES('" + scriptTypeCode + _
                                    "','" + scriptTypeCode + "',"
        oOutFile.WriteLine "        '" + script + "')"
        oOutFile.WriteBlankLines 1
        oOutFile.WriteLine "        SELECT @ScriptId = IDENT_CURRENT('SCRIPT')"
        oOutFile.WriteLine "        INSERT INTO script_questionnaireversion_map (scriptid, questionnaireversionid) VALUES (@ScriptId, @QVId)"
        oOutFile.WriteLine "    END"
        
        oInFile.Close( )
    Next
    If scriptType = "Humans" Then
        Set oInFile = oFs.OpenTextFile( scriptDirectory + "\ApprovalScript.vbs", 1, false )
        script = Replace( oInFile.ReadAll, "'", "''" )
        oOutFile.WriteBlankLines 1
        oOutFile.WriteLine "BEGIN"
        oOutFile.WriteLine "    UPDATE Script SET ScriptText="
        oOutFile.WriteLine "    '" + script + "' WHERE Type = 'SCRIPTTYPE_APPROVAL'"
        oOutFile.WriteLine "END"
        oInFile.Close( )
        
        Set oInFile = oFs.OpenTextFile( scriptDirectory + "\SubmissionScript.vbs", 1, false )
        script = Replace( oInFile.ReadAll, "'", "''" )
        oOutFile.WriteBlankLines 1
        oOutFile.WriteLine "BEGIN"
        oOutFile.WriteLine "    UPDATE Script SET ScriptText="
        oOutFile.WriteLine "    '" + script + "' WHERE Type = 'SCRIPTTYPE_SUBMISSION' AND NOT EXISTS (SELECT NULL FROM Script_Questionnaireversion_Map WHERE ScriptId = Script.Id)"
        oOutFile.WriteLine "END"
        
    End If
    oOutFile.Close( )
Next